package com.atguigu.gmall_sugar.mapper;

import com.atguigu.gmall_sugar.bean.ProductStats;
import lombok.Data;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.web.bind.annotation.RequestParam;

import java.math.BigDecimal;
import java.util.List;

/**
 * @ClassName: ProductStatsMapper
 * @Description:
 * @Author: kele
 * @Date: 2021/4/28 15:58
 **/
@Mapper
public interface ProductStatsMapper {

    //总的成交额统计   ---数字翻牌表
    @Select("select \n" +
            " sum(order_amount) order_amount\n" +
            "from product_stats_2021\n" +
            "where toYYYYMMDD(stt)=#{date}")
   BigDecimal getGMV(int date);


    /**
     * 商品交易统计
     */
    //1、统计某天不同 品类 交易额排名
    @Select("select \n" +
            "  tm_id,\n" +
            "  tm_name,\n" +
            "  sum(order_amount) order_amount\n" +
            " from product_stats_2021\n" +
            " where toYYYYMMDD(stt)=#{date}\n" +
            " group by tm_id, tm_name\n" +
            " order by order_amount desc\n" +
            " limit #{limit}")
    List<ProductStats> getProductStatsByTM(@Param("date")  int date,
                                           @Param("limit") int limit);



    //2、统计某天不同 品牌的商品 交易额排名    --在滚动表中

    @Select("select\n" +
            "  spu_id,\n" +
            "  spu_name,\n" +
            "  sum(order_amount) order_amount," +
            "  sum(order_ct) order_ct\n" +
            " from product_stats_2021\n" +
            " where toYYYYMMDD(stt)=#{date}\n" +
            " group by spu_id, spu_name\n" +
            " order by order_amount desc\n" +
            " limit #{limit}")
    List<ProductStats> getProductStatsBySpu(@Param("date")  int date,
                                            @Param("limit") int limit);


    //3、统计某天 不同种类 交易额排名

    @Select("select\n" +
            "  category3_id,\n" +
            "  category3_name,\n" +
            "  sum(order_amount) order_amount,\n" +
            "  sum(order_ct) order_ct\n" +
            " from product_stats_2021\n" +
            " where toYYYYMMDD(stt)=#{date}\n" +
            " group by category3_id, category3_name\n" +
            " order by order_amount desc\n" +
            " limit #{limit}")
    List<ProductStats> getProductStatsByCategory(@Param("date")  int date,
                                            @Param("limit") int limit);
}
